package com.gome.axwl.aoc.common.util;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelOprateUtil {

    private final static Logger log = LoggerFactory.getLogger(ExcelOprateUtil.class);

    public static List<String> getDcCodeList(MultipartFile file) throws IOException, InvalidFormatException {
        List<String> dcCodeList = new ArrayList<>();
        InputStream is = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet = workbook.getSheetAt(0);           //默认读取第一个sheet
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            Cell cell = row.getCell(0);     //默认读取第一列
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                dcCodeList.add(cell.getStringCellValue().trim());
            } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                continue;
            } else {
                dcCodeList.add("000000");
            }
        }
        return dcCodeList;
    }

    public static File appendMsgToRow(List<Integer> errRowList, MultipartFile file) throws IOException, InvalidFormatException {
        InputStream is = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet = workbook.getSheetAt(0);           //默认读取第一个sheet
        for (Integer rowNum : errRowList) {
            Row row = sheet.getRow(rowNum + 1);
            Cell cell = row.createCell(1);
            cell.setCellValue("DC编码错误.");
            CellStyle cellStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setColor(Font.COLOR_RED);      //红字
            font.setBold(true);                 //加粗
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
        FileOutputStream fos = new FileOutputStream("a");
        workbook.write(fos);
        File newFile = File.createTempFile("tmp", null);
        fos = new FileOutputStream(newFile);
        return newFile;
    }

    public static XSSFWorkbook createExcel(List<String> list){
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("DC编码表");
        XSSFRow title = sheet.createRow(0);
        XSSFCell cell = title.createCell(0);
        cell.setCellValue("DC编码");
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
       // font.setColor(Font.COLOR_RED);      //红字
        font.setBold(true);                 //加粗
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        for(int i=0; i< list.size(); i++){
            XSSFRow row = sheet.createRow(i+1);
            XSSFCell cell1 = row.createCell(0);
            if(list.get(i).indexOf("-") == -1){
                cell1.setCellValue(list.get(i));
            }else{
                cell1.setCellValue(list.get(i).replaceAll("-",""));
                XSSFCell cell2 = row.createCell(1);
                cell2.setCellValue("dc编码有误");
            }
        }
       return wb;
    }

}

